//
//  CZStatusDAL.swift
//  微博08
//
//  Created by apple on 16/3/13.
//  Copyright © 2016年 itcast. All rights reserved.
//

import UIKit

/*
    加载微博数据,有可能从本地,也有可能从网络加载
        1.查看本地是否有数据 *
        2.如果本地有数据直接返回
        3.本地没有数据发送网络请求
        4.获取到数据后保存到数据库 *
        5.将获取的数据返回给调用的人
*/
class CZStatusDAL: NSObject {
    static let sharedDAL = CZStatusDAL()
    
//    private let overTime: NSTimeInterval = 7 * 24 * 60 * 60
    private let overTime: NSTimeInterval = 60
    
    func loadStatus(since_id: Int, max_id: Int, dalLoadStatusCallBack: (statuses: [[String: AnyObject]]?, error: NSError?) -> ()) {
        // 1.查看本地是否有数据 *
        loadCacheStatus(since_id, max_id: max_id) { (statuses) -> () in
            if statuses != nil && statuses?.count > 0 {
                print("本地加载到了数据: \(statuses?.count)")
                // 2.如果本地有数据直接返回
                dalLoadStatusCallBack(statuses: statuses!, error: nil)
                return
            }
            
            // 3.本地没有数据发送网络请求
            CZNetworkTool.sharedInstance.loadStatus(since_id, max_id: max_id, finished: { (result, error) -> () in
                if error != nil {
                    print("error: \(error)")
                    dalLoadStatusCallBack(statuses: nil, error: error)
                    return
                }
                
                if let array = result?["statuses"] as? [[String: AnyObject]] {
                    // 4.获取到数据后保存到数据库 *
                    self.saveStatus(array)
                    
                    // 5.将获取的数据返回给调用的人
                    dalLoadStatusCallBack(statuses: array, error: nil)
                } else {
                    dalLoadStatusCallBack(statuses: nil, error: nil)
                }
            })
        }
    }
    
    /**
     查看本地是否有数据
     
     - parameter since_id: 下拉刷新要获取比这个大的id
     - parameter max_id: 上拉加载更多,加载比这个id小的id
     */
    private func loadCacheStatus(since_id: Int, max_id: Int, loadCacheStatusCallBack: (statuses: [[String: AnyObject]]?) -> ()) {
        var sql = "SELECT status_id, status, user_id FROM T_Status \n"
        
        let uid = CZUserAccountViewModel.sharedInstance.userAccount!.uid!
        
        if since_id > 0 {
            // 下拉刷新,加载比 since_id大的微博
            sql += "WHERE status_id > \(since_id) AND user_id = \(uid) \n"
        } else if max_id > 0 {
            // 下拉刷新,加载比 since_id大的微博
            sql += "WHERE status_id < \(max_id) AND user_id = \(uid)\n"
        }
        
        sql += " ORDER BY status_id DESC LIMIT 20;"
        
        print("拼接的sql: \(sql)")
        
        // 查询数据
        CZSQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
            guard let resultSet = db.executeQuery(sql) else {
                print("没有数据")
                loadCacheStatusCallBack(statuses: nil)
                return
            }
            
            // 本地加载到的微博数据字典
            var statuses = [[String: AnyObject]]()
            
            while resultSet.next() {
//                let status_id = resultSet?.intForColumn("status_id")
                
                let status = resultSet.stringForColumn("status")
                
                // String -> data -> 字典
                let data = status?.dataUsingEncoding(NSUTF8StringEncoding)
                
                let dict = try! NSJSONSerialization.JSONObjectWithData(data!, options: NSJSONReadingOptions(rawValue: 0)) as! [String: AnyObject]
                
//                print("本地加载到的微博数据: \(status)")
                statuses.append(dict)
            }
            
            // 回调
            loadCacheStatusCallBack(statuses: statuses)
        }
    }
    
    /**
     获取到数据后保存到数据库
     
     - parameter statuses: 微博字典数组
     */
    private func saveStatus(statuses: [[String: AnyObject]]) {
        
        let uid = CZUserAccountViewModel.sharedInstance.userAccount!.uid!
        // 只能用在插入数据非时候 OR REPLACE
        // 将主键一样的数据进行替换, 主键不能是自动增长的
        let sql = "INSERT OR REPLACE INTO T_Status (status_id, status, user_id) VALUES (?, ?, ?)"
        CZSQLiteManager.sharedManager.queue.inTransaction { (db, rollback) -> Void in
            
            for status in statuses {
                let id = status["id"] as! Int
                
                // 将字典转成String保存到数据库
                // 字典 -> data -> String
                let data = try! NSJSONSerialization.dataWithJSONObject(status, options: NSJSONWritingOptions(rawValue: 0))
                
                let string = String(data: data, encoding: NSUTF8StringEncoding)
                
                db.executeUpdate(sql, id, string!, uid)
            }
            
            print("保存微博数据完成: \(statuses.count) 条")
        }
    }
    
    
    //  清楚微博数据
    func clearStatus() {
        // 计算超时时间
        let date = NSDate(timeIntervalSinceNow: -overTime)
        print("过期时间: \(date)")
        
        // 日期格式化
        let df = NSDateFormatter()
        df.dateFormat = "yyyy-MM-dd HH:mm:ss"
        df.locale = NSLocale(localeIdentifier: "EN")
        let overDate = df.stringFromDate(date)
        
        let sql = "DELETE FROM T_Status WHERE create_at < '\(overDate)'"
        
        print("sql: \(sql)")
        
        // 执行sql
        CZSQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
            db.executeUpdate(sql)
        }
    }
}
